﻿using Dapper;
using System.Data.SqlClient;
using yunbo.Model.BIP;

namespace yunbo.Repositorys.BIPRepositorys
{
    public class MBD_GXRepository
    {
        public string data = "Data Source=121.37.93.71;Initial Catalog=FanXingMES;User ID=sa;Password=geili@2025;";
        /// <summary>
        /// 查询
        /// </summary>
        /// <returns></returns>
        public List<Mbd_GX> GetMbd_GX()
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"select gx.gx_id, gx_name,gx_code,gxlx_code,gxlx_name,gxzb_time,gxbz_time,d.dictionary_name as bgfsname,bgfs,zjbz_code,gx_code ,gx_state from mbd_gx as gx INNER JOIN  dictionary as d ON gx.bgfs = d.dictionary_id  where is_deleted=1";   
                List<Mbd_GX> gx = connection.Query<Mbd_GX>(sql).ToList();
                return gx; 
            }
        }

        //修改状态
        public int UpdateStatus(string gx_code, int gx_state, int update_by)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                // 使用参数化查询来防止 SQL 注入
                string sql = "\tUPDATE mbd_gx SET gx_state = @gx_state, update_by = @update_by, update_time = GETDATE() WHERE gx_code = @gx_code";

                // 创建命令对象并设置参数
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@gx_state", gx_state);
                    command.Parameters.AddWithValue("@update_by", update_by);
                    command.Parameters.AddWithValue("@gx_code", gx_code); // 注意：如果 cp_code 是数值类型，则不应该加引号

                    // 打开连接并执行命令
                    connection.Open();
                    int affectedRows = command.ExecuteNonQuery();

                    // 返回受影响的行数
                    return affectedRows;
                }
            }
        }
        //删除
        public int UpdateDelete(string gx_code, int is_delete, int update_by)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                // 使用参数化查询来防止 SQL 注入
                string sql = "UPDATE mbd_gx SET is_deleted = @is_deleted, update_by = @update_by, update_time = GETDATE() WHERE gx_code = @gx_code;";

                // 创建命令对象并设置参数
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@is_deleted", is_delete);
                    command.Parameters.AddWithValue("@update_by", update_by);
                    command.Parameters.AddWithValue("@gx_code", gx_code); 

                    // 打开连接并执行命令
                    connection.Open();
                    int affectedRows = command.ExecuteNonQuery();

                    // 返回受影响的行数
                    return affectedRows;
                }
            }
        }

        //新增
        public int AddMbd_gx(Mbd_GX mbd_GX)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                // 使用参数化查询来防止 SQL 注入
                string sql = @"INSERT INTO mbd_gx (gx_name, gx_code, gxlx_code, gxlx_name, gxzb_time, gxbz_time, bgfs, zjbz_code, gx_state, is_deleted, create_by, create_time)
                           VALUES (@gx_name, @gx_code, @gxlx_code, @gxlx_name, @gxzb_time, @gxbz_time, @bgfs, @zjbz_code, @gx_state, @is_deleted, @create_by, @create_time);
                           SELECT SCOPE_IDENTITY();";

                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    // 添加参数
                    command.Parameters.AddWithValue("@gx_name", mbd_GX.gx_name);
                    command.Parameters.AddWithValue("@gx_code", mbd_GX.gx_code);
                    command.Parameters.AddWithValue("@gxlx_code", mbd_GX.gxlx_code);
                    command.Parameters.AddWithValue("@gxlx_name", mbd_GX.gxlx_name);
                    command.Parameters.AddWithValue("@gxzb_time", mbd_GX.gxzb_time);
                    command.Parameters.AddWithValue("@gxbz_time", mbd_GX.gxbz_time);
                    command.Parameters.AddWithValue("@bgfs", mbd_GX.bgfs);
                    command.Parameters.AddWithValue("@zjbz_code", mbd_GX.zjbz_code);
                    command.Parameters.AddWithValue("@gx_state", mbd_GX.gx_state);
                    command.Parameters.AddWithValue("@is_deleted", mbd_GX.is_deleted);
                    command.Parameters.AddWithValue("@create_by", mbd_GX.create_by);
                    command.Parameters.AddWithValue("@create_time", mbd_GX.create_time);

                    // 打开连接并执行命令
                    connection.Open();
                    int affectedRows = command.ExecuteNonQuery();

                    // 返回受影响的行数
                    return affectedRows;
                }
            }
        }

        //修改
        public int UpdateMbd_GX(Mbd_GXs mbd_GX)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                // 使用参数化查询来防止 SQL 注入  
                string sql = @"UPDATE mbd_gx   
                   SET gx_name = @gx_name,   
                       gxlx_code = @gxlx_code,   
                       gxlx_name = @gxlx_name,   
                       gxzb_time = @gxzb_time,   
                       gxbz_time = @gxbz_time,   
                       bgfs = @bgfs,   
                       zjbz_code = @zjbz_code,   
                       update_by = @update_by,   
                       update_time = @update_time  
                   WHERE gx_code = @gx_code;";

                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    // 添加参数  
                    command.Parameters.AddWithValue("@gx_name", mbd_GX.gx_name);
                    command.Parameters.AddWithValue("@gxlx_code", mbd_GX.gxlx_code);
                    command.Parameters.AddWithValue("@gxlx_name", mbd_GX.gxlx_name);
                    command.Parameters.AddWithValue("@gxzb_time", mbd_GX.gxzb_time);
                    command.Parameters.AddWithValue("@gxbz_time", mbd_GX.gxbz_time);
                    command.Parameters.AddWithValue("@bgfs", mbd_GX.bgfs);
                    command.Parameters.AddWithValue("@zjbz_code", mbd_GX.zjbz_code);
                    command.Parameters.AddWithValue("@update_by", mbd_GX.update_by);
                    command.Parameters.AddWithValue("@update_time", mbd_GX.update_time);
                    command.Parameters.AddWithValue("@gx_code", mbd_GX.gx_code); // 需要用来定位更新的记录  

                    // 打开连接并执行命令  
                    connection.Open();
                    int affectedRows = command.ExecuteNonQuery();

                    // 返回受影响的行数  
                    return affectedRows;
                }
            }
        }
        public CXMdb_GX GetMbd_GXCode(string code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"select * from [dbo].[mbd_gx]  where gx_code='{code}'";
                CXMdb_GX gx = connection.Query<CXMdb_GX>(sql).FirstOrDefault();
                return gx;
            }
        }
    }
}
